IF EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'VW_InvoiceFetch')
	BEGIN
		DROP  View VW_InvoiceFetch
	END
GO

CREATE View VW_InvoiceFetch AS

SELECT
	dbo.FN_CleanMoneyValue(SUM(InvoiceItems.Amount)) AS Total,
	i.InvoiceId, i.AccountId, i.TypeFlag, i.InvoiceRef, i.RecipientRef,
	i.CreateDate, i.UpdateDate, i.InvoiceDate,
	a.Code AS AccountCode, a.Title AS AccountTitle
FROM Invoices AS i
INNER JOIN Accounts AS a
	ON a.AccountId = i.AccountId
INNER JOIN (
	SELECT SUM(Rate * Quantity) AS Amount, InvoiceId
	FROM InvoiceItems
	GROUP BY InvoiceItemId, InvoiceId
) AS InvoiceItems ON InvoiceItems.InvoiceId = i.InvoiceId
GROUP BY
	i.InvoiceId, i.AccountId, i.TypeFlag,
	i.InvoiceRef, i.RecipientRef,
	i.CreateDate, i.UpdateDate, i.InvoiceDate,
	a.Code, a.Title
	
GO